package com.etc.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.rowset.CachedRowSet;

import com.etc.dao.IEmployeesDao;
import com.etc.entity.Dept;
import com.etc.entity.Employees;
import com.etc.util.DBUtil;
import com.etc.util.PageData;

public class EmployeesDaoImpl implements IEmployeesDao {
	DeptDaoImpl deptDao=new DeptDaoImpl();
	
	/**
	 * 根据员工id查询员工
	 * empId:员工id
	 * return:员工对象
	 */
	@Override
	public Employees queryById(int empId) {
		// TODO Auto-generated method stub
		CachedRowSet crs = DBUtil.executeQuery("select * from employees where empId=?", empId);
		Dept dept ;
		Employees emp=null;
		try {
			while(crs.next()) {

				int deptId = crs.getInt(2);
				String empName = crs.getString(3);
				String empPwd = crs.getString(4);
				String empPhone = crs.getString(5);
				String hireDate = crs.getString(6);
				String empPicture = crs.getString(7);
				int empStatus = crs.getInt(8);
				
				dept = deptDao.queryById(deptId);
				emp=new Employees(empId, empName, empPwd, empPhone, hireDate, dept, empPicture, empStatus);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return emp;
	}
	/**
	 * 查询所有员工
	 * return:员工集合
	 */
	@Override
	public List<Employees> queryAllEmployees() {
		Employees employees=null;
		Dept dept = new Dept();
		List<Employees> list =new  ArrayList<Employees>();
		CachedRowSet rs = DBUtil.executeQuery("select * from employees A inner join dept B on A.deptId=B.deptId");
		try {
			while(rs.next()) {
				Integer empId = rs.getInt("empId");
				String empName = rs.getString("empName");
				String empPwd = rs.getString("empPwd");
				String empPhone = rs.getString("empPhone");
				String hireDate = rs.getString("hireDate");
				String empPicture = rs.getString("empPicture");
				Integer empStatus = rs.getInt("empStatus");
				
				dept.setDeptName(rs.getString("deptName"));

				employees = new Employees(empId, empName, empPwd, empPhone, hireDate, dept, empPicture, empStatus);
				list.add(employees);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 根据名字和密码查询员工
	 * empname:员工姓名
	 * emppwd:员工密码
	 * return:员工对象
	 */
	@Override
	public Employees queryEmpByNameAndPwd(String empname, String emppwd) {
		Employees employees= null;
		CachedRowSet crs=DBUtil.executeQuery("select a.empId,a.deptId,a.empName,a.empPwd,a.empPhone,a.hireDate,a.empPicture,a.empStatus,b.deptName from employees a LEFT JOIN dept b on a.deptId=b.deptId where empName=? and empPwd=? ",empname,emppwd);
		try {
			while(crs.next()){
				int empId=crs.getInt(1);
				int dept1=crs.getInt(2);
				String empName=crs.getString(3);
				String empPwd=crs.getString(4);
				String empPhone=crs.getString(5);
				String hireDate=crs.getString(6);			
				String empPicture=crs.getString(7);
				int empStatus=crs.getInt(8);
				String deptName=crs.getString(9);
				Dept dept=new Dept(dept1,deptName);
				employees=new Employees(empId, empName, empPwd, empPhone, hireDate, dept, empPicture, empStatus);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return employees;
	}
	/**
	 * 分页查询
	 * 查询所有员工
	 * return:员工集合
	 */
	@Override
	public PageData queryAllEmployeesd(int pageNo, int pageSize) {
		Employees employees=null;
		Dept dept = null;	
		List<Employees> list =new  ArrayList<Employees>();
		String sql="select * from employees A inner join dept B on A.deptId=B.deptId limit ?,?";
		CachedRowSet rs = DBUtil.executeQuery(sql,(pageNo-1)*pageSize,pageSize);		
		try {
			while(rs.next()) {
				Integer empId = rs.getInt("empId");
				String empName = rs.getString("empName");
				String empPwd = rs.getString("empPwd");
				String empPhone = rs.getString("empPhone");
				String hireDate = rs.getString("hireDate");
				String empPicture = rs.getString("empPicture");
				Integer empStatus = rs.getInt("empStatus");
				
				Integer deptId = rs.getInt("deptId");
				String deptName = rs.getString("deptName");
				dept =new Dept(deptId, deptName);				
				employees = new Employees(empId, empName, empPwd, empPhone, hireDate, dept, empPicture, empStatus);
				list.add(employees);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		int totalCount=0;
		CachedRowSet crs1 = DBUtil.executeQuery("select count(*) from employees A inner join dept B on A.deptId=B.deptId " );
		try {
			while(crs1.next()) {
				totalCount = crs1.getInt(1);				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		PageData PageData=new PageData(list,pageNo,pageSize,totalCount);
		return PageData;
	}
	
	/**
	 * 添加员工
	 * return:ture
	 */
	@Override
	public boolean addEmployees(Employees employees,int deptId) {
		return DBUtil.executeUpdate("insert into employees values(null,?,?,?,?,now(),?,?)",deptId,employees.getEmpName(),employees.getEmpPwd(),employees.getEmpPhone(),employees.getEmpPicture(),employees.getEmpStatus());
	}
	/**
	 * 修改员工
	 * return:ture
	 */
	@Override
	public boolean modifyEmployees(Employees employees,int deptId) {
		return DBUtil.executeUpdate("update employees set deptId=?,empName =?,empPwd =?,empPhone =?, hireDate =?,empStatus =? where empId=?;",deptId,employees.getEmpName(),employees.getEmpPwd(),employees.getEmpPhone(),employees.getHireDate(),employees.getEmpStatus(),employees.getEmpId());
	}
	/**
	 * 修改员工头像
	 * return:ture
	 */
	@Override
	public boolean modifyEmpPic(Employees employees) {
		return DBUtil.executeUpdate("update employees set empPicture = ? where empId=?;",employees.getEmpPicture(),employees.getEmpId());
	}
	
	/**
	 * 根据id删除员工(将员工的状态改为离职)
	 * return:ture
	 */
	@Override
	public boolean delsEmployees(int empId) {
		return DBUtil.executeUpdate("update employees set empStatus =0 where empId=?;",empId);
	}
	/**
	 * 按多条件分页查询
	 * 查询所有员工
	 * return:员工集合
	 */
	@Override
	public PageData queryKeyEmployeesd(int pageNo, int pageSize, int empId1, String empName1, int deptId1, int empStatus1,
			String hireDate1, String hireDate2) {
		Employees employees=null;
		Dept dept = null;
		CachedRowSet rs=null;
		CachedRowSet crs1=null;
		List<Employees> list =new  ArrayList<Employees>();
		//查询所有
		if(empId1==0 && empName1=="" && deptId1==0 && empStatus1==88 && hireDate1=="" && hireDate2=="" ){
			String sql="select * from employees A inner join dept B on A.deptId=B.deptId limit ?,?";
			 rs = DBUtil.executeQuery(sql,(pageNo-1)*pageSize,pageSize);
			 crs1 = DBUtil.executeQuery("select count(*) from employees A inner join dept B on A.deptId=B.deptId " );
		}
		//按部门查询
		if(empId1==0 && empName1=="" && deptId1!=0 && empStatus1==88 && hireDate1=="" && hireDate2=="" ){
			String sql="select * from employees A inner join dept B on A.deptId=B.deptId where A.deptId like ? limit ?,?";
			 rs = DBUtil.executeQuery(sql,deptId1,(pageNo-1)*pageSize,pageSize);
			 crs1 = DBUtil.executeQuery("select count(*) from employees A inner join dept B on A.deptId=B.deptId where A.deptId like ?" ,deptId1);
		}
		//按员工ID查询
		if(empId1!=0 && empName1=="" && deptId1==0 && empStatus1==88 && hireDate1=="" && hireDate2=="" ){
			String sql="select * from employees A inner join dept B on A.deptId=B.deptId where A.empId like ? limit ?,?";
			 rs = DBUtil.executeQuery(sql,empId1,(pageNo-1)*pageSize,pageSize);
			 crs1 = DBUtil.executeQuery("select count(*) from employees A inner join dept B on A.deptId=B.deptId where A.empId like ?" ,empId1);
		}
		//按员工姓名模糊查询
		if(empId1==0 && empName1!="" && deptId1==0 && empStatus1==88 && hireDate1=="" && hireDate2=="" ){
			String sql="select * from employees A inner join dept B on A.deptId=B.deptId where A.empName like ? limit ?,?";
			rs = DBUtil.executeQuery(sql,"%"+empName1+"%",(pageNo-1)*pageSize,pageSize);
			crs1 = DBUtil.executeQuery("select count(*) from employees A inner join dept B on A.deptId=B.deptId where A.empName like ?" ,"%"+empName1+"%");
		}
		//按员工状态查询
		if(empId1==0 && empName1=="" && deptId1==0 && empStatus1!=88 && hireDate1=="" && hireDate2=="" ){
			String sql="select * from employees A inner join dept B on A.deptId=B.deptId where A.empStatus like ? limit ?,?";
			rs = DBUtil.executeQuery(sql,empStatus1,(pageNo-1)*pageSize,pageSize);
			crs1 = DBUtil.executeQuery("select count(*) from employees A inner join dept B on A.deptId=B.deptId where A.empStatus like ?" ,empStatus1);
		}
		//按入职时间之间查询
		if(empId1==0 && empName1=="" && deptId1==0 && empStatus1==88 && hireDate1!="" && hireDate2!="" ){			
				String sql="select * from employees A inner join dept B on A.deptId=B.deptId where A.hireDate BETWEEN ? AND ? limit ?,?";
				rs = DBUtil.executeQuery(sql,hireDate1+" 00:00",hireDate2+" 23:59:59",(pageNo-1)*pageSize,pageSize);
				crs1 = DBUtil.executeQuery("select count(*) from employees A inner join dept B on A.deptId=B.deptId where A.hireDate BETWEEN ? AND ?" ,hireDate1+" 00:00",hireDate2+" 23:59:59");					
		}
		//按入职起始时间查询
		if(empId1==0 && empName1=="" && deptId1==0 && empStatus1==88 && hireDate1!="" && hireDate2=="" ){
			String sql="select * from employees A inner join dept B on A.deptId=B.deptId where A.hireDate BETWEEN ? AND ? limit ?,?";
			rs = DBUtil.executeQuery(sql,hireDate1,"2199-12-30",(pageNo-1)*pageSize,pageSize);
			crs1 = DBUtil.executeQuery("select count(*) from employees A inner join dept B on A.deptId=B.deptId where A.hireDate BETWEEN ? AND ?" ,hireDate1,"2199-12-30");
		}
			
		//按部门和姓名模糊查询
		if(empId1==0 && empName1!="" && deptId1!=0 && empStatus1==88 && hireDate1=="" && hireDate2=="" ){
			String sql="select * from employees A inner join dept B on A.deptId=B.deptId where A.empName like ? AND A.deptId like ? limit ?,?";
			rs = DBUtil.executeQuery(sql,"%"+empName1+"%",deptId1,(pageNo-1)*pageSize,pageSize);
			crs1 = DBUtil.executeQuery("select count(*) from employees A inner join dept B on A.deptId=B.deptId where A.empName like ? AND A.deptId like ?" ,"%"+empName1+"%",deptId1);
		}
		//按部门和员工状态查询
		if(empId1==0 && empName1=="" && deptId1!=0 && empStatus1!=88 && hireDate1=="" && hireDate2=="" ){
			String sql="select * from employees A inner join dept B on A.deptId=B.deptId where A.empStatus like ? AND A.deptId like ? limit ?,?";
			rs = DBUtil.executeQuery(sql,empStatus1,deptId1,(pageNo-1)*pageSize,pageSize);
			crs1 = DBUtil.executeQuery("select count(*) from employees A inner join dept B on A.deptId=B.deptId where A.empStatus like ? AND A.deptId like ?" ,empStatus1,deptId1);
		}	
		//按部门和入职时间查询
		if(empId1==0 && empName1=="" && deptId1!=0 && empStatus1==88 && hireDate1!="" && hireDate2!="" ){
			String sql="select * from employees A inner join dept B on A.deptId=B.deptId where A.hireDate BETWEEN ? AND ? AND A.deptId like ? limit ?,?";
			rs = DBUtil.executeQuery(sql,hireDate1+" 00:00",hireDate2+" 23:59:59",deptId1,(pageNo-1)*pageSize,pageSize);
			crs1 = DBUtil.executeQuery("select count(*) from employees A inner join dept B on A.deptId=B.deptId where A.hireDate BETWEEN ? AND ? AND A.deptId like ?" ,hireDate1+" 00:00",hireDate2+" 23:59:59",deptId1);
		}	
		//按姓名和员工状态模糊查询
		if(empId1==0 && empName1!="" && deptId1==0 && empStatus1!=88 && hireDate1=="" && hireDate2=="" ){
			String sql="select * from employees A inner join dept B on A.deptId=B.deptId where A.empStatus like ? AND A.empName like ? limit ?,?";
			rs = DBUtil.executeQuery(sql,empStatus1,"%"+empName1+"%",(pageNo-1)*pageSize,pageSize);
			crs1 = DBUtil.executeQuery("select count(*) from employees A inner join dept B on A.deptId=B.deptId where A.empStatus like ? AND A.empName like ?" ,empStatus1,"%"+empName1+"%");
		}	
		//按姓名和入职时间模糊查询
		if(empId1==0 && empName1!="" && deptId1==0 && empStatus1==88 && hireDate1!="" && hireDate2!="" ){
			String sql="select * from employees A inner join dept B on A.deptId=B.deptId where A.hireDate BETWEEN ? AND ? AND A.empName like ? limit ?,?";
			rs = DBUtil.executeQuery(sql,hireDate1+" 00:00",hireDate2+" 23:59:59","%"+empName1+"%",(pageNo-1)*pageSize,pageSize);
			crs1 = DBUtil.executeQuery("select count(*) from employees A inner join dept B on A.deptId=B.deptId where A.hireDate BETWEEN ? AND ? AND A.empName like ?" ,hireDate1+" 00:00",hireDate2+" 23:59:59","%"+empName1+"%");
		}	
		//按员工状态和入职时间模糊查询
		if(empId1==0 && empName1=="" && deptId1==0 && empStatus1!=88 && hireDate1!="" && hireDate2!="" ){
			String sql="select * from employees A inner join dept B on A.deptId=B.deptId where A.hireDate BETWEEN ? AND ? AND A.empStatus like ? limit ?,?";
			rs = DBUtil.executeQuery(sql,hireDate1+" 00:00",hireDate2+" 23:59:59",empStatus1,(pageNo-1)*pageSize,pageSize);
			crs1 = DBUtil.executeQuery("select count(*) from employees A inner join dept B on A.deptId=B.deptId where A.hireDate BETWEEN ? AND ? AND A.empStatus like ?" ,hireDate1+" 00:00",hireDate2+" 23:59:59",empStatus1);
		}	
		
		//按部门和姓名,工状态,入职时间模糊查询
		if(empId1==0 && empName1!="" && deptId1!=0 && empStatus1!=88 && hireDate1!="" && hireDate2!="" ){
			String sql="select * from employees A inner join dept B on A.deptId=B.deptId where A.hireDate BETWEEN ? AND ? AND A.empStatus like ? AND A.empName like ? AND A.deptId like ? limit ?,?";
			rs = DBUtil.executeQuery(sql,hireDate1+" 00:00",hireDate2+" 23:59:59",empStatus1,"%"+empName1+"%",deptId1,(pageNo-1)*pageSize,pageSize);
			crs1 = DBUtil.executeQuery("select count(*) from employees A inner join dept B on A.deptId=B.deptId where A.hireDate BETWEEN ? AND ? AND A.empStatus like ? AND A.empName like ? AND A.deptId like ?" ,hireDate1+" 00:00",hireDate2+" 23:59:59",empStatus1,"%"+empName1+"%",deptId1);
		}	
				
		//按部门和姓名,工状态模糊查询
		if(empId1==0 && empName1!="" && deptId1!=0 && empStatus1!=88 && hireDate1=="" && hireDate2=="" ){
			String sql="select * from employees A inner join dept B on A.deptId=B.deptId where A.empStatus like ? AND A.empName like ? AND A.deptId like ? limit ?,?";
			rs = DBUtil.executeQuery(sql,empStatus1,"%"+empName1+"%",deptId1,(pageNo-1)*pageSize,pageSize);
			crs1 = DBUtil.executeQuery("select count(*) from employees A inner join dept B on A.deptId=B.deptId where A.empStatus like ? AND A.empName like ? AND A.deptId like ?" ,empStatus1,"%"+empName1+"%",deptId1);
		}
				//按部门和姓名,入职时间模糊查询
		if(empId1==0 && empName1!="" && deptId1!=0 && empStatus1==88 && hireDate1!="" && hireDate2!="" ){
			String sql="select * from employees A inner join dept B on A.deptId=B.deptId where A.hireDate BETWEEN ? AND ? AND A.empName like ? AND A.deptId like ? limit ?,?";
			rs = DBUtil.executeQuery(sql,hireDate1+" 00:00",hireDate2+" 23:59:59","%"+empName1+"%",deptId1,(pageNo-1)*pageSize,pageSize);
			crs1 = DBUtil.executeQuery("select count(*) from employees A inner join dept B on A.deptId=B.deptId where A.hireDate BETWEEN ? AND ? AND A.empName like ? AND A.deptId like ?" ,hireDate1+" 00:00",hireDate2+" 23:59:59","%"+empName1+"%",deptId1);
		}	
				//按姓名,工状态,入职时间模糊查询
		if(empId1==0 && empName1!="" && deptId1==0 && empStatus1!=88 && hireDate1!="" && hireDate2!="" ){
			String sql="select * from employees A inner join dept B on A.deptId=B.deptId where A.hireDate BETWEEN ? AND ? AND A.empStatus like ? AND A.empName like ? limit ?,?";
			rs = DBUtil.executeQuery(sql,hireDate1+" 00:00",hireDate2+" 23:59:59",empStatus1,"%"+empName1+"%",(pageNo-1)*pageSize,pageSize);
			crs1 = DBUtil.executeQuery("select count(*) from employees A inner join dept B on A.deptId=B.deptId where A.hireDate BETWEEN ? AND ? AND A.empStatus like ? AND A.empName like ?" ,hireDate1+" 00:00",hireDate2+" 23:59:59",empStatus1,"%"+empName1+"%");
		}	
		//按部门和工状态,入职时间模糊查询
		if(empId1==0 && empName1=="" && deptId1!=0 && empStatus1!=88 && hireDate1!="" && hireDate2!="" ){
			String sql="select * from employees A inner join dept B on A.deptId=B.deptId where A.hireDate BETWEEN ? AND ? AND A.empStatus like ? AND A.deptId like ? limit ?,?";
			rs = DBUtil.executeQuery(sql,hireDate1+" 00:00",hireDate2+" 23:59:59",empStatus1,deptId1,(pageNo-1)*pageSize,pageSize);
			crs1 = DBUtil.executeQuery("select count(*) from employees A inner join dept B on A.deptId=B.deptId where A.hireDate BETWEEN ? AND ? AND A.empStatus like ? AND A.deptId like ?" ,hireDate1+" 00:00",hireDate2+" 23:59:59",empStatus1,deptId1);
		}	
		try {
			while(rs.next()) {
				Integer empId = rs.getInt("empId");
				String empName = rs.getString("empName");
				String empPwd = rs.getString("empPwd");
				String empPhone = rs.getString("empPhone");
				String hireDate = rs.getString("hireDate");
				String empPicture = rs.getString("empPicture");
				Integer empStatus = rs.getInt("empStatus");
				
				Integer deptId = rs.getInt("deptId");
				String deptName = rs.getString("deptName");
				dept =new Dept(deptId, deptName);				
				employees = new Employees(empId, empName, empPwd, empPhone, hireDate, dept, empPicture, empStatus);
				list.add(employees);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		int totalCount=0;		
		try {
			while(crs1.next()) {
				totalCount = crs1.getInt(1);				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		PageData PageData=new PageData(list,pageNo,pageSize,totalCount);
		return PageData;
	}
	
}
